#!/usr/bin/perl

use Cwd;

$baseurl = getcwd;

if ($ARGV[0] eq 'CREATING') {
    $mode = 'CREATING';
} else {
    $mode = 'UPDATING';
}

if ($ARGV[1] =~ /\//) {
    ($baseurl = $ARGV[1]) =~ s/[\/]*$//;
}

$error_outs = $baseurl . '/errors_Checkins.txt';
$print_outs = $baseurl . '/prints_Checkins.txt';

binmode(STDOUT, ':utf8');

open (STDERR, ">$error_outs");
open (STDOUT, ">$print_outs");

print "baseurl: " . $baseurl . "\n";

# local system variables
$connect = $baseurl . '/connect.pl';
require $connect;

use IO::File;

#NOTE: IN ORDER TO USE THIS SCRIPT, YOU MUST FIRST QUERY A SET OF CHECKIN RECORDS

#THEN YOU MUST "EXPORT" INFORMATION FROM CATALOG
#for instance:

#RECORD #(CHECKIN)   SCODE1  SCODE2  LOCATION    RLOC    CREATED(CHECKIN)    UPDATED(CHECKIN)    CKIN INFO   CHECK-IN    ERM LINK    LIB. HAS(CHECKIN)   CALL #(CHECKIN) RECORD #(BIBLIO)    CALL #(BIBLIO)

#THE RESULTING TEXT FILE NEEDS TO BE LOADED INTO ./checkins/ 

if ($ARGV[0] eq 'CREATING') {
$next_checkin_record = 100000;
}

&hashes_and_arrays; #SUBROUTINE AT END OF SCRIPT

($HOUR, $MIN, $SEC) = (localtime)[2, 1, 0];
$start_time = "$HOUR:$MIN:$SEC";

$folder_name = 'checkins';

$where = $baseurl . '/' . $folder_name;

while (defined($next_file = <$where/*.out>)) {

print "filename: " . $next_file . "\n";

($batch_textfiles = $next_file) =~ s/checkins/checkins_textfiles/g;

$batch_loader = IO::File->new("> $batch_textfiles") || die "cannot open $batch_textfiles for writing: $!\n";
flock($batch_loader, 2);

    open(IN, "< $next_file") || die "cannot open $next_file for reading: $!";

        while(<IN>) {

            if($_ !~ /^RECORD #\(CHECKIN\)/) {
                @fields = split(/\t/);

                %query_insert = (
                    checkin_record => '',
                    scode1 => '', 
                    checkin_status => '', #scode2
                    cloc => '',
                    rloc => '',
                    checkin_created => '',
                    checkin_updated => '',
                    latest_received => '', 
                    ckin_info => '',
                    erm_link => '',
                    lib_has => '',
                    checkin_callnumber => '',
                    record_owner => $institution,
                    local_control_number => '',
                    universal_identifier => ''
                );

##START: CREATE DELETED CHECKIN RECORDS

if ($ARGV[0] eq 'CREATING') {

$this_checkin_record = substr($fields[0], 1, 6);

        while($next_checkin_record < $this_checkin_record) {

            $query_insert{'checkin_record'} = $institution . 'c' . $next_checkin_record;
            $query_insert{'checkin_status'} = 'x';

            @keys = keys %query_insert;
            @values = values %query_insert;
            
            $mysql_keys = join(", ", @keys);
            $mysql_values = join("\t", @values);
            $mysql_values =~ s/\\\t/\\ \t/g;

            print $batch_loader $mysql_values . "\n";
        
            $next_checkin_record++;
        }

        $next_checkin_record++;

}       

##END: CREATE DELETED CHECKIN RECORDS

##START: DERIVE latest_received AND PUT AFTER latest_received_url\TAB

#if($fields[7] ne '') {
#my @ckin_info_elements = split(';', $fields[7]);
#
#print substr($fields[0], 1, 6) . "\t" . $ckin_info_elements[1] . "\n";
#
#    for($i = 2; $i <= $#ckin_info_elements; $i++) {
#        $h = $i - 1;
#        if($ckin_info_elements[$i] =~ /1\.[0-9]* A/) {
#            print $ckin_info_elements[$h] . "\n";
##            print $ckin_info_elements[$i] . "\n";
#        }
#    }
#
#}

##END: DERIVE latest_received AND PUT AFTER latest_received_url\TAB


###START: CREATE CHECKIN RECORDS

        $query_insert{'checkin_record'} = $institution . 'c' . substr($fields[0], 1, 6);
        $query_insert{'scode1'} = $fields[1];
        $query_insert{'checkin_status'} = $fields[2]; #scode2
        @locations = split(",", $fields[3]);
        $query_insert{'rloc'} = $fields[4];
        $query_insert{'checkin_created'} = $fields[5];
        $query_insert{'checkin_updated'} = $fields[6];
        $query_insert{'ckin_info'} = $fields[7];
        $query_insert{'erm_link'} = $fields[8];
        ($query_insert{'lib_has'} = $fields[9]) =~ s/,/, /g;
        ($query_insert{'checkin_callnumber'} = $fields[10]) =~ s/^[\s]*//;

                foreach $date_field( checkin_created, checkin_updated ) {
                    if($query_insert{$date_field} =~ /([0-9]{2})-([0-9]{2})-([0-9]{4})/) {
                        $query_insert{$date_field} = $3 . "-" . $1 . "-" . $2;
                    } elsif($query_insert{$date_field} =~ /([0-9]{2})-([0-9]{2})-([0-9]{2})/) {
                        $query_insert{$date_field} = "19" . $3 . "-" . $1 . "-" . $2;
                    }
                }

                @local_control_numbers = ();
                @callnumbers = ();

                $diff = $#fields - 11;

                $limit = $diff/2;

                for($a = 0; $a < $limit; $a++) {
                    $b = 11 + $a;
                    $c = $b + 1 + $limit;
                    
                    push(@local_control_numbers, $institution . substr($fields[$b], 0, 8));

if ($query_insert{'ckin_info'} ne '' && $query_insert{'checkin_status'} !~ /[ns]/) {
$checkin_record_digits = substr($fields[0], 1, 6);
$query_insert{'latest_received'} = 'http://holmes.lib.muohio.edu/search~S9?/.' . $fields[$b] . '/.' . $fields[$b] . '/1,1,1,B/crC' . $checkin_record_digits . '&FF=&1,0,';
}
                    $bib_callnumber = $fields[$c];
                    chomp($bib_callnumber);
                    $bib_callnumber =~ s/^[\s]*//;
                    if($query_insert{'checkin_callnumber'} eq '') {
                        push(@callnumbers, $bib_callnumber);
                    } else {
                        push(@callnumbers, $query_insert{'checkin_callnumber'});
                    }

                }

###END: CREATE CHECKIN RECORDS

####START: PRINT VALUES TO BATCH FILES

    foreach $this_cloc (@locations) {

        $query_insert{'cloc'} = $institution . '\t' . $this_cloc;


        for($a = 0; $a < $limit; $a++) {

        $query_insert{'local_control_number'} = $local_control_numbers[$a];
        $query_insert{'checkin_callnumber'} = $callnumbers[$a];

        @keys = keys %query_insert;
        @values = values %query_insert;

            $mysql_keys = join(", ", @keys);
            $mysql_values = join("\t", @values);
            $mysql_values =~ s/\\\t/\\ \t/g;

            print $batch_loader $mysql_values . "\n";

        }

####END: PRINT VALUES TO BATCH FILES

}

                undef($mysql_values);
                undef(%query_insert);
                undef(@keys);
                undef(@values);
                undef(@local_control_numbers);
                undef(@callnumbers);

        }
    
    }

    close(IN) || "cannot close $next_file: $!";

unlink($next_file);

    $batch_loader->autoflush(1);
    close($batch_loader, 2) || "cannot close $batch_loader: $!";

}

$checkins_keys_textfiles = $baseurl . '/' . $folder_name . '_textfiles/checkins.key';

$checkins_keys_loader = IO::File->new("> $checkins_keys_textfiles") || die "cannot open $checkins_keys_textfiles for writing: $!\n";
binmode( $checkins_keys_loader, ':utf8' );
flock($checkins_keys_loader, 2);

    print $checkins_keys_loader $mysql_keys . "\n";

        $checkins_keys_loader->autoflush(1);
        close($checkins_keys_loader, 2) || "cannot close $checkins_keys_loader: $!";

($HOUR, $MIN, $SEC) = (localtime)[2, 1, 0];
$end_of_file_creation_time = "$HOUR:$MIN:$SEC";

use DBI;

# connection for the select
$dbh = DBI->connect("DBI:mysql:database=$db_name;host=$db_host", $db_user, $db_pass, { mysql_enable_utf8 =>1 }) or die "couldn't connect to database: " . DBI->errstr;

@current_time = localtime;

$last_update_time = ($current_time[5] + 1900) . "-" . ($current_time[4] + 1) . "-$current_time[3] $current_time[2]:$current_time[1]:$current_time[0]";

print "last_update_time: " . $last_update_time . "\n";


if ($ARGV[0] eq 'CREATING') {
    $sth = $dbh->do("TRUNCATE TABLE $db_local_tables_hash{'checkins_record_table'}");
    $this_table = $db_local_tables_hash{'checkins_record_table'};
} else {
    $sth = $dbh->do("TRUNCATE TABLE $db_local_tables_hash{'checkins_record_temp_table'}");
    $this_table = $db_local_tables_hash{'checkins_record_temp_table'};
}

$where = $baseurl . '/' . $folder_name . '_textfiles';

#####START: INITIAL TABLE LOAD

#if ($ARGV[0] eq 'CREATING') {
#    $dbh->do("ALTER TABLE " . $this_table . " DISABLE KEYS");
#}

while (defined($next_file = <$where/*.out>)) {

    $sth2 = $dbh->do("LOAD DATA INFILE '" . $next_file . "' REPLACE INTO TABLE $this_table FIELDS TERMINATED BY '\t' (" . $mysql_keys . ")") || print "intial load died in local\n";

unlink($next_file);

}

unlink($checkins_keys_textfiles);

#if ($ARGV[0] eq 'CREATING') {
#    $dbh->do("ALTER TABLE " . $this_table . " ENABLE KEYS");
#}


#####END: INITIAL TABLE LOAD

######START: UPDATE ACTUAL CHECKIN TABLE WITH DATA IN TEMP TABLE

if ($ARGV[0] ne 'CREATING') {

$this_table = $db_local_tables_hash{'checkins_record_table'};
$that_table = $db_local_tables_hash{'checkins_record_temp_table'};

    $querystring = "SHOW FIELDS FROM $this_table";

        $query = $dbh->prepare($querystring)
            or die("couldn't prepare statement: ". $dbh->errstr);

        $query->execute()
            or die("couldn't execute statement: ". $dbh->errstr);

    while ( @row = $query->fetchrow_array ) {
        push (@keys, $row[0]); ## field name
    }

$query->finish();

pop(@keys);

$mysql_keys = join(", ", @keys);

@update_strings = ();

    foreach $field_name (@keys) {
        push(@update_strings, $this_table . '.' . $field_name . ' = ' . $that_table . '.' . $field_name);
    }

    $temp_updates = join(", ", @update_strings);

        $querystring = "UPDATE $this_table, $that_table
            SET $temp_updates
            WHERE $this_table.checkin_record = $that_table.checkin_record
            AND $this_table.local_control_number = $that_table.local_control_number";

        $query = $dbh->prepare($querystring)
            or die("couldn't prepare statement: ". $dbh->errstr);

        $query->execute()
            or print "couldn't execute statement: $querystring";

        $query->finish();

        $querystring = "DELETE $that_table.*
            FROM $that_table
            LEFT JOIN $this_table
            ON $that_table.checkin_record = $this_table.checkin_record
            WHERE $that_table.checkin_record = $this_table.checkin_record
            AND $that_table.local_control_number = $this_table.local_control_number";

        $query = $dbh->prepare($querystring)
            or die("couldn't prepare statement: ". $dbh->errstr);

        $query->execute()
            or print "couldn't execute statement: $querystring";

        $query->finish();

        $querystring = "INSERT $this_table
            ($mysql_keys) 
            SELECT $mysql_keys from $that_table";

        $query = $dbh->prepare($querystring)
            or die("couldn't prepare statement: ". $dbh->errstr);

        $query->execute()
            or print "couldn't execute statement: $querystring";

        $query->finish();
            
}

######END: UPDATE ACTUAL ITEM TABLE WITH DATA IN TEMP TABLE

#######START: UPDATE $that_table TABLE WITH universal_identifiers

$this_table = $db_local_tables_hash{'bibs_record_table'};
$that_table = $db_local_tables_hash{'checkins_record_table'};

$update_string = "UPDATE $that_table, $this_table
set $that_table.universal_identifier = $this_table.universal_identifier
WHERE $that_table.solr_update >= ?
AND $that_table.local_control_number = $this_table.local_control_number";

print $update_string . "\n" . $last_update_time . "\n";


$query = $dbh->prepare($update_string)
                    or die("couldn't prepare statement: ". $dbh->errstr);

                $query->execute($last_update_time)
                    or die("couldn't execute statement: ". $dbh->errstr);

                $query->finish();

#######END: UPDATE $that_table TABLE WITH universal_identifiers


########START: UPDATE $that_table TABLE

$this_table = $db_local_tables_hash{'checkins_record_table'};
$that_table = $db_local_tables_hash{'bibs_record_table'};

$update_string = "UPDATE $that_table, $this_table
set $that_table.solr_update = NOW()
WHERE $this_table.solr_update >= ?
AND $that_table.local_control_number = $this_table.local_control_number";

$query = $dbh->prepare($update_string)
                    or die("couldn't prepare statement: ". $dbh->errstr);

                $query->execute($last_update_time)
                    or die("couldn't execute statement: ". $dbh->errstr);

        $query->finish();

########END: UPDATE $that_table TABLE

#########START: UPDATE $that_table TABLE

$that_table = $db_local_tables_hash{'holdings_record_table'};

             $update_string = "UPDATE $that_table, $this_table
             set $that_table.lib_has = $this_table.lib_has,
                $that_table.latest_received = $this_table.latest_received
             WHERE $this_table.solr_update >= ?
             AND $that_table.local_control_number = $this_table.local_control_number
             AND $that_table.hloc = $this_table.cloc";

        $query = $dbh->prepare($update_string)
            or die("couldn't prepare statement: ". $dbh->errstr);

        $query->execute($last_update_time)
            or die("couldn't execute statement: ". $dbh->errstr);

        $query->finish();

#########END: UPDATE $that_table TABLE

##########START: CREATING FILES FOR LOAD INTO CONSORTIUM CHECKINS, HOLDINGS AND BIBS TABLES


if ($consortium ne '') {

($HOUR, $MIN, $SEC) = (localtime)[2, 1, 0];
$start_external_update_time = "$HOUR:$MIN:$SEC";

foreach $table_name (checkin, holding, bib) {

$this_table = $db_local_tables_hash{$table_name . 's_record_table'};

$recordcount = 0;

    $querystring = "SHOW FIELDS FROM $this_table";

        $query = $dbh->prepare($querystring)
            or die("couldn't prepare statement: ". $dbh->errstr);

        $query->execute()
            or die("couldn't execute statement: ". $dbh->errstr);

    while ( @row = $query->fetchrow_array ) {
        push (@fields_array, $row[0]); ## field name
    }

$query->finish();

pop(@fields_array);

$fields_array_string = join(", ", @fields_array);

$consortium_keys_textfiles = $baseurl . '/consortium_' . $table_name . 's_textfiles/consortium' . $table_name . 's.key';

$consortium_keys_loader = IO::File->new("> $consortium_keys_textfiles") || die "cannot open $consortium_keys_textfiles for writing: $!\n";
binmode( $consortium_keys_loader, ':utf8' );
flock($consortium_keys_loader, 2);

    print $consortium_keys_loader $fields_array_string . "\n";

        $consortium_keys_loader->autoflush(1);
        close($consortium_keys_loader, 2) || "cannot close $consortium_keys_loader: $!";

if ($ARGV[0] eq 'CREATING') {
    $IN_string = "'n', 's', 'z', 'x'";
} else {
    $IN_string = "'n', 's', 'z'";
}

$limit_start = 0;
$rownumber = 1;
$status = $table_name . '_status';
while($rownumber > 0) {

        $querystring = "SELECT * from $this_table
            WHERE solr_update >= ?
            AND $status NOT IN ($IN_string)
            LIMIT ?, 10000";

        $query = $dbh->prepare($querystring)
            or die("couldn't prepare statement: ". $dbh->errstr);

        $query->execute($last_update_time, $limit_start)
            or die("couldn't execute statement: ". $dbh->errstr);

$limit_start += 10000;
$rownumber = $query->rows();

while(@query_array = $query->fetchrow_array()) {
    $int = $recordcount / 10000;

    if ($int =~ m/^\d+$/) {

        if($recordcount > 0) {
        $consortium_batch_loader->autoflush(1);
        close($consortium_batch_loader, 2) || "cannot close $consortium_batch_loader: $!";
        }

$consortium_batch_textfiles = $baseurl . '/consortium_' . $table_name . 's_textfiles/consortium_from_3' . $folder_name  . '_' . $table_name . $recordcount .  '.txt';

$consortium_batch_loader = IO::File->new("> $consortium_batch_textfiles") || die "cannot open $consortium_batch_textfiles for writing: $!\n";
binmode( $consortium_batch_loader, ':utf8' );
flock($consortium_batch_loader, 2);

    }

$recordcount++;

pop(@query_array);

foreach(@query_array) {
    s/\t/\\t/g;
}

    $query_array_string = join("\t", @query_array);
    $query_array_string =~ s/\\\t/\\ \t/g;
    $query_array_string =~ s/\n/\\n/g;

    print $consortium_batch_loader $query_array_string . "\n";
    $line_count++;
    }

}

$query->finish();

if($recordcount > 0) {
        $consortium_batch_loader->autoflush(1);
        close($consortium_batch_loader, 2) || "cannot close $consortium_batch_loader: $!";
}

undef(@fields_array);

}

#########END: CREATING FILES FOR LOAD INTO CONSORTIUM CHECKINS, HOLDINGS AND BIBS TABLES

($HOUR, $MIN, $SEC) = (localtime)[2, 1, 0];
$end_external_update_time = "$HOUR:$MIN:$SEC";

}

$dbh->disconnect;

($HOUR, $MIN, $SEC) = (localtime)[2, 1, 0];
$end_time = "$HOUR:$MIN:$SEC";

print "Start time: " . $start_time . "\nEnd of file creation time: " . $end_of_file_creation_time . "\nEnd time: " . $end_time . "\n";


#SUBROUTINES

sub hashes_and_arrays {

$hash_files = $baseurl . '/hashfiles';

while (defined($next_hash_file = <$hash_files/*.pl>)) {
require $next_hash_file;
}

}

